
import pymysql
from config import *
# 处理数据库相关

class DataBase:
    def __init__(self, host, account, password, database_name, port) -> None:
        self.host = host
        self.account = account
        self.password = password
        self.database = database_name
        self.port = port

    def isUser(self, stuid) -> bool:
        '''
        判断是否是用户
        '''
        values = (stuid,)
        sql = '''
            select *
            from login
            where stuid = %s
        '''
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        res = False
        try:
            cursor.execute(sql, values)
            user_info = cursor.fetchall()
            if user_info:
                res = True
        except Exception as e:
            print('执行sql语句时出错')
            db.rollback()
        finally:
            db.close()
        return res

    def getUserInfoById(self, user_id):
        '''
        通过id获取user信息
        '''
        values = (user_id, )
        sql = '''
            select id, stuid, qq_num
            from login
            where id = %s
        '''
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        res = tuple()
        try:
            cursor.execute(sql, values)
            res = cursor.fetchall()[0]
        except:
            print('执行sql语句时出错')
            db.rollback()
        finally:
            db.close()
        return res
    
    def isUserById(self, id):
        '''
        通过学号获取对应密码（即QQ号）来进行用户验证
        '''
        values = (id, )
        sql = '''
            select *
            from login
            where id = %s
        '''
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        status = False
        try:
            cursor.execute(sql, values)
            res = cursor.fetchall()
            if res:
                status = True
        except:
            print('执行sql语句时出错')
            db.rollback()
        finally:
            db.close()
        return status

    def getId(self, stuid):
        '''
        通过学号来获取对应ID号
        '''
        values = (stuid,)
        sql = '''
            select id
            from login
            where stuid = %s
        '''
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        res = tuple()
        try:
            cursor.execute(sql, values)
            res = cursor.fetchall()[0]
        except:
            print('执行sql语句时出错')
            db.rollback()
        finally:
            db.close()
        return res

    def insertClass(self, name, day, room, teacher, week, section, stuid, qq):
        '''
        向数据库中插入课程
        '''
        values = (name, day, room, teacher, week, section, stuid, qq)
        sql = '''
            insert into hnuclass(name, day, room, teacher, week, section, stuid, qq)
            values(%s, %s, %s, %s, %s, %s, %s, %s)
        '''
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        try:
            cursor.execute(sql, values)
            db.commit()
        except:
            print('插入课程出错')
            db.rollback()
        finally:
            db.close()

    
    def getAllClass(self, stuid):
        '''
        通过学号查询所有课程
        '''
        values = (stuid, )
        sql = '''
            select *
            from hnuclass
            where stuid = %s
        '''
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        res = tuple()
        try:
            cursor.execute(sql, values)
            res = cursor.fetchall()
        except:
            print('执行sql语句时出错')
            db.rollback()
        finally:
            db.close()
        return res

database = DataBase(DATABASE_ADDRESS, DATABASE_ACCOUNT, DATABASE_PASSWORD, DATABASE_NAME, DATABASE_PORT)